﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CRD_ITEM_BIZ_Get_Using_Amount_By_Account_At_Base_Dt')
	BEGIN
		DROP Procedure usp_UPDMS_CRD_ITEM_BIZ_Get_Using_Amount_By_Account_At_Base_Dt
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_CRD_ITEM_BIZ_Get_Using_Amount_By_Account_At_Base_Dt
**	Desc : 기준일별결제금액
**	Test Exec Query : Exec usp_UPDMS_CRD_ITEM_BIZ_Get_Using_Amount_By_Account_At_Base_Dt 'CC06X', '2014-02-13', 'ko'
**	Called by : Crd_Dac_UPDMS_CRD_ITEM_BIZ.cs
**	Program ID : Crd2020r
**	Auth : 송시명
**	Date : 2014-02-13
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CRD_ITEM_BIZ_Get_Using_Amount_By_Account_At_Base_Dt]
@ls_card_cd nvarchar(max),
@ls_base_dt nvarchar(10),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @pre_year_dt nvarchar(10)
DECLARE @pre_3_month_dt nvarchar(10)
DECLARE @pre_2_month_dt nvarchar(10)
DECLARE @pre_1_month_dt nvarchar(10)

SET @pre_year_dt = CONVERT(VARCHAR(10), DATEADD(yy, -1, CONVERT(DATETIME, @ls_base_dt)),121)
SET @pre_3_month_dt = CONVERT(VARCHAR(10), DATEADD(mm, -3, CONVERT(DATETIME, @ls_base_dt)),121)
SET @pre_2_month_dt = CONVERT(VARCHAR(10), DATEADD(mm, -2, CONVERT(DATETIME, @ls_base_dt)),121)
SET @pre_1_month_dt = CONVERT(VARCHAR(10), DATEADD(mm, -1, CONVERT(DATETIME, @ls_base_dt)),121)

--데이터
SELECT Account,
       Account_Nm,
       ISNULL([PY], 0) AS Pre_Year_Dt,
       ISNULL([PM3], 0) AS Pre_3_Month_Dt,
       ISNULL([PM2], 0) AS Pre_2_Month_Dt,
       ISNULL([PM1], 0) AS Pre_1_Month_Dt,
       ISNULL([CM], 0) AS Base_Dt,
       ISNULL([CM], 0) - ISNULL([PY], 0) AS Pre_Year_Base_Diff,
       ISNULL([CM], 0) - ISNULL([PM3], 0) AS Pre_3_Month_Base_Diff,
       ISNULL([CM], 0) - ISNULL([PM2], 0) AS Pre_2_Month_Base_Diff,
       ISNULL([CM], 0) - ISNULL([PM1], 0) AS Pre_1_Month_Base_Diff
  FROM (
       SELECT Div,
              Account,
              dbo.ufn_UPDMS_Get_Code_Name(x.Account, 'CRD', @ls_lang_set) AS Account_Nm,
              Amount
         FROM (
              SELECT 'PY' AS [Div], --전년
                     ucib.Account,
                     SUM(ucib.Amount) AS Amount
                FROM UPDMS_CRD_ITEM_BIZ ucib WITH(NOLOCK)
                JOIN UPDMS_CRD_PAYMNT_DT ucpd WITH(NOLOCK) ON ucib.Card_Cd = ucpd.Card_Cd
               WHERE ucib.Biz_Div = 'AB001'
			     AND ucib.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
                 AND @pre_year_dt BETWEEN Mass_St_Dt AND Mass_Ed_Dt
                 AND ucib.Biz_Dt >= ucpd.Mass_St_Dt
                 AND ucib.Biz_Dt <= @pre_year_dt
               GROUP BY ucib.Account
              UNION ALL
              SELECT 'PM3' AS [Div], --3개월전
                     ucib.Account,
                     SUM(ucib.Amount) AS Amount
                FROM UPDMS_CRD_ITEM_BIZ ucib WITH(NOLOCK)
                JOIN UPDMS_CRD_PAYMNT_DT ucpd WITH(NOLOCK) ON ucib.Card_Cd = ucpd.Card_Cd
               WHERE ucib.Biz_Div = 'AB001'
			     AND ucib.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
                 AND @pre_3_month_dt BETWEEN Mass_St_Dt AND Mass_Ed_Dt
                 AND ucib.Biz_Dt >= ucpd.Mass_St_Dt
                 AND ucib.Biz_Dt <= @pre_3_month_dt
               GROUP BY ucib.Account
              UNION ALL
              SELECT 'PM2' AS [Div], --2개월전
                     ucib.Account,
                     SUM(ucib.Amount) AS Amount
                FROM UPDMS_CRD_ITEM_BIZ ucib WITH(NOLOCK)
                JOIN UPDMS_CRD_PAYMNT_DT ucpd WITH(NOLOCK) ON ucib.Card_Cd = ucpd.Card_Cd
               WHERE ucib.Biz_Div = 'AB001'
			     AND ucib.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
                 AND @pre_2_month_dt BETWEEN Mass_St_Dt AND Mass_Ed_Dt
                 AND ucib.Biz_Dt >= ucpd.Mass_St_Dt
                 AND ucib.Biz_Dt <= @pre_2_month_dt
               GROUP BY ucib.Account
              UNION ALL
              SELECT 'PM1' AS [Div], --전월
                     ucib.Account,
                     SUM(ucib.Amount) AS Amount
                FROM UPDMS_CRD_ITEM_BIZ ucib WITH(NOLOCK)
                JOIN UPDMS_CRD_PAYMNT_DT ucpd WITH(NOLOCK) ON ucib.Card_Cd = ucpd.Card_Cd
               WHERE ucib.Biz_Div = 'AB001'
			     AND ucib.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
                 AND @pre_1_month_dt BETWEEN Mass_St_Dt AND Mass_Ed_Dt
                 AND ucib.Biz_Dt >= ucpd.Mass_St_Dt
                 AND ucib.Biz_Dt <= @pre_1_month_dt
               GROUP BY ucib.Account
              UNION ALL 
              SELECT 'CM' AS [Div], --기준일
                     ucib.Account,
                     SUM(ucib.Amount) AS Amount
                FROM UPDMS_CRD_ITEM_BIZ ucib WITH(NOLOCK)
                JOIN UPDMS_CRD_PAYMNT_DT ucpd WITH(NOLOCK) ON ucib.Card_Cd = ucpd.Card_Cd
               WHERE ucib.Biz_Div = 'AB001'
			     AND ucib.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
                 AND @ls_base_dt BETWEEN Mass_St_Dt AND Mass_Ed_Dt
                 AND ucib.Biz_Dt >= ucpd.Mass_St_Dt
                 AND ucib.Biz_Dt <= @ls_base_dt
               GROUP BY ucib.Account
              ) x
       ) pv
 PIVOT (
        SUM(pv.Amount) FOR pv.Div IN ([PY],[PM3],[PM2],[PM1],[CM]) 
       ) AS pvt
 ORDER BY pvt.Account

GO
